﻿Imports System.Linq
Imports System.Data.SqlClient
Public Class frmTeacherSchedule
    Dim CN As New SqlConnection
    Dim TeacherID As Integer
    Dim availability As Dictionary(Of Char, String)
    Private Sub frmTeacherSchedule_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        CN.ConnectionString = My.Settings.IT308ConnectionString
        availability = New Dictionary(Of Char, String)
        availability.Add(CChar("a"), "Monday Morning")
        availability.Add(CChar("b"), "Monday Afternoon")
        availability.Add(CChar("c"), "Monday Evening")
        availability.Add(CChar("d"), "Tuesday Morning")
        availability.Add(CChar("e"), "Tuesday Afternoon")
        availability.Add(CChar("f"), "Tuesday Evening")
        availability.Add(CChar("g"), "Wednesday Morning")
        availability.Add(CChar("h"), "Wednesday Afternoon")
        availability.Add(CChar("i"), "Wednesday Evening")
        availability.Add(CChar("j"), "Thursday Morning")
        availability.Add(CChar("k"), "Thursday Afternoon")
        availability.Add(CChar("l"), "Thursday Evening")
        availability.Add(CChar("m"), "Friday Morning")
        availability.Add(CChar("n"), "Friday Afternoon")
        availability.Add(CChar("o"), "Friday Evening")

        'WB - creates the connection to database
        Dim LI As New ListViewItem
        Dim cmd As New SqlCommand
        cmd.Connection = CN
        cmd.CommandText = "SELECT CLASSES.CoursesID, COURSES.Name, CLASSES.ClassTime, count(STUDENTSCLASSES.StudentsID) as StudentCount FROM CLASSES inner join TEACHER on CLASSES.TeacherID = TEACHER.ID inner join STUDENTSCLASSES on CLASSES.ID = STUDENTSCLASSES.ClassesID inner join COURSES on CLASSES.CoursesID = COURSES.ID WHERE CLASSES.TeacherID = @TeacherID GROUP BY CLASSES.COURSESID, COURSES.Name, CLASSES.ClassTime, TEACHER.LName, TEACHER.FName"
        Dim Reader As SqlDataReader = Nothing
        cmd.Parameters.AddWithValue("@TeacherID", TeacherID)

        'WB - tries to open the connection, reports an error it it fails
        Try
            CN.Open()
        Catch ex As Exception
            MessageBox.Show("Could not connect to the Database - " & ex.Message, "Error")
        End Try

        'WB - clears all the items out of lsvCourse
        lsvClasses.Items.Clear()
        'lstStudents.Items.Clear()

        'WB - calls the procedure to generate the possibilites and places the courses in lsvCourses
        Try
            Reader = cmd.ExecuteReader
            While Reader.Read
                LI = New ListViewItem
                LI.Text = Convert.ToString(Reader.Item("Name"))
                LI.SubItems.Add(Convert.ToString(Reader.Item("CoursesID")))
                LI.SubItems.Add(availability(Convert.ToChar(Reader.Item("ClassTime"))))
                LI.SubItems.Add(Convert.ToString(Reader.Item("StudentCount")))
                lsvClasses.Items.Add(LI)
            End While
            Reader.Close()
        Catch ex As Exception
            MessageBox.Show("Cannot Load Currently Scheduled Classes - " & ex.Message, "Error")
        Finally
            Try
                Reader.Close()
            Catch ex As Exception
            End Try
            If CN.State = ConnectionState.Open Then CN.Close()
        End Try
    End Sub
    Public Sub New(ByVal ID As Integer)
        Me.InitializeComponent()
        TeacherID = ID
    End Sub

    Private Sub btnOK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOK.Click
        End
    End Sub
    Private Sub load_lstStudents()
        'WB - creates the connection to the database. and loads the paramaters for the query
        Dim cmd As New SqlCommand
        Dim foo = From entry In availability Where entry.Value = lsvClasses.SelectedItems(0).SubItems.Item(2).Text Select entry.Key
        cmd.Connection = CN
        cmd.CommandText = "SELECT DISTINCT STUDENTS.FName, STUDENTS.LName FROM STUDENTSCLASSES inner join STUDENTS on STUDENTS.ID = STUDENTSCLASSES.StudentsID	inner join CLASSES on STUDENTSCLASSES.ClassesID = CLASSES.ID WHERE CLASSES.CoursesID = @CourseID AND ClassTime = @ClassTime"
        Dim Reader As SqlDataReader = Nothing
        cmd.Parameters.AddWithValue("@CourseID", lsvClasses.SelectedItems(0).SubItems.Item(1).Text)
        cmd.Parameters.AddWithValue("@ClassTime", foo.First.ToString)

        'WB - tries to open the connection and reports the error if it fails.
        Try
            CN.Open()
        Catch ex As Exception
            MessageBox.Show("Cannot connect to Database - " & ex.Message, "Error")
        End Try

        'WB - clears the combo box
        lstStudents.Items.Clear()

        'WB - executes the query and loads the Students names into lstStudents
        Try
            Reader = cmd.ExecuteReader
            While Reader.Read
                lstStudents.Items.Add(Convert.ToString(Reader.Item("FName")) & " " & Convert.ToString(Reader.Item("LName")))
            End While
        Catch ex As Exception
            MessageBox.Show("Cannot load Students - " & ex.Message, "Error")
        Finally
            If CN.State = ConnectionState.Open Then CN.Close()
            Reader.Close()
        End Try
    End Sub

    Private Sub lsvClasses_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lsvClasses.Click
        load_lstStudents()
    End Sub
End Class